﻿using System;

using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using TimeTableTool.DTO;
using System.Globalization;

namespace TimeTableTool
{
    class StationDao 
    {
        SQLiteConnection connection;

        public StationDao(SQLiteConnection connection)
        {
            this.connection = connection;
        }

       
      

        public StationDTO GetStation(string name)
        {
            SQLiteCommand selectCmnd = new SQLiteCommand(connection);
            selectCmnd.CommandText = "SELECT * FROM station WHERE name LIKE @nameValue";
            selectCmnd.Parameters.AddWithValue("nameValue", name);

            List<StationDTO> stations = GetStations(selectCmnd);
            if (stations.Count == 1)
            {
                return stations[0];
            }
            else
                if (stations.Count==0)
                {
                    return null;
                }
                else 
                {
                    throw new ArgumentException("Search string found more than one station");
                }
        }

        public StationDTO GetStation(int id)
        {
            SQLiteCommand selectCmnd = new SQLiteCommand(connection);
            selectCmnd.CommandText = "SELECT * FROM station WHERE id=@idValue";
            selectCmnd.Parameters.AddWithValue("idValue", id);

            List<StationDTO> stations = GetStations(selectCmnd);
            if (stations.Count == 1)
            {
                return stations[0];
            }
            else
                if (stations.Count == 0)
                {
                    throw new ArgumentException("The station with this ID was not found");
                }
                else
                {
                    throw new ArgumentException("The ID found more record - this should not happen");
                }
                
        }

        public List<StationDTO> GetAllStations()
        {
            SQLiteCommand selectCmnd = new SQLiteCommand(connection);
            selectCmnd.CommandText = "SELECT * FROM station";
            return GetStations(selectCmnd);
        }

        public List<String> GetAllStationNames()
        {
            SQLiteCommand selectCmnd = new SQLiteCommand(connection);
            selectCmnd.CommandText = "SELECT * FROM station";

            using (selectCmnd)
            {
                using (SQLiteDataReader reader = selectCmnd.ExecuteReader())
                {
                    List<String> stationList = new List<String>();
                    while (reader.Read())
                    {
                        stationList.Add(reader[DBHelper.STATION_NAME].ToString());
                    }
                    return stationList;
                }

            }
        }

        public List<ReacheableStationDTO> GetReacheableStations(int startId, DateTime time)
        {
            SQLiteCommand command = new SQLiteCommand(connection);
            command.CommandText = "SELECT line_id,station_id,time, day_type FROM time_table WHERE start_station_id=@startId";
            command.Parameters.AddWithValue("startId", startId);

            return GetReacheableStations(command);

        }

        public void insertStation(Station station)
        {
            SQLiteCommand inserCmnd = new SQLiteCommand(connection);
            inserCmnd.CommandText = "INSERT INTO station (id, name, normalized_name, longtitude, latitude) VALUES (@idValue, @nameValue, @normalizedNameValue, @longtitudeValue, @latitudeValue)";
            inserCmnd.Parameters.AddWithValue("idValue", station.id);
            inserCmnd.Parameters.AddWithValue("nameValue", station.Name);
            inserCmnd.Parameters.AddWithValue("normalizedNameValue", RemoveDiacritics(station.Name));
            inserCmnd.Parameters.AddWithValue("longtitudeValue", station.lon);
            inserCmnd.Parameters.AddWithValue("latitudeValue", station.lat);
            inserCmnd.ExecuteNonQuery();
        }

        public static string RemoveDiacritics(string s) {
            s = s.Normalize(NormalizationForm.FormD);
            StringBuilder sb = new StringBuilder();
         
            for (int i = 0; i < s.Length; i++) {
                if (CharUnicodeInfo.GetUnicodeCategory(s[i]) != UnicodeCategory.NonSpacingMark) sb.Append(s[i]);
            }
         
            return sb.ToString();
        }

        private List<ReacheableStationDTO> GetReacheableStations(SQLiteCommand cmnd)
        {
            using (cmnd)
            {
                using (SQLiteDataReader reader = cmnd.ExecuteReader())
                {
                    List<ReacheableStationDTO> stationList = new List<ReacheableStationDTO>();
                    while (reader.Read())
                    {
                        ReacheableStationDTO station = new ReacheableStationDTO();
                        station.TimeTableId = int.Parse(reader["id"].ToString());
                        station.StationId = int.Parse(reader["destination_station_id"].ToString());
                        station.Distance = int.Parse(reader["time_distance"].ToString());
                        station.LineId = int.Parse(reader["line_id"].ToString());
                        stationList.Add(station);
                    }
                    return stationList;
                }

            }
        }

        private List<StationDTO> GetStations(SQLiteCommand cmnd)
        {
            using (cmnd)
            {
                using (SQLiteDataReader reader = cmnd.ExecuteReader())
                {
                    List<StationDTO> stationList = new List<StationDTO>();
                    while (reader.Read())
                    {
                        StationDTO station = new StationDTO();
                        station.Name = reader[DBHelper.STATION_NAME].ToString();
                        station.Id = int.Parse(reader[DBHelper.STATION_ID].ToString());
                        String latitudeString = reader["latitude"].ToString();
                        if (latitudeString.Length>0)
                        {
                            station.Latitude = int.Parse(latitudeString);
                        }
                        else
                        {
                            station.Latitude = null;
                        }
                        String longtitudeString = reader["longtitude"].ToString();
                        if (longtitudeString.Length > 0)
                        {
                            station.Longtitude = int.Parse(longtitudeString);
                        }
                        else
                        {
                            station.Longtitude = null;
                        }
                        stationList.Add(station);
                    }
                    return stationList;
                }

            }
        }
    }
}
